//
//  DataBaseHandle.m
//  UI18_数据库第三方_练习
//
//  Created by dllo on 15/12/2.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "DataBaseHandle.h"
#import "FMDB.h"
#import "Student.h"

/*
 sql指令(以Student举例):
 
 创建表单 : @"CREATE TABLE IF NOT EXISTS student(number integer PRIMARY KEY AUTOINCREMENT, name TEXT, sex TEXT, age integer)"
 
 删除表单 : @"DROP TABLE student"
 
 插入数据 : @"INSERT INTO student(name, sex, age) VALUES ('%@', '%@', '%ld')", stu.name, stu.sex, stu.age
 
 删除数据 : @"DELETE FROM student WHERE number = '%ld'", num
 
 修改数据(按num) : @"UPDATE student SET name = '%@', sex = '%@', age = '%ld' WHERE number = '%ld'", stu.name, stu.sex, stu.age, num
 
 查询所有数据 : SELECT * FROM student
 
 按分类查找(按sex) : @"SELECT * FROM student WHERE  sex LIKE '%%%@%%'", sex
 
 */
@interface DataBaseHandle ()
@property (nonatomic, retain) FMDatabase *db;
@end


@implementation DataBaseHandle
- (void)dealloc
{
    [_db release];
    [super dealloc];
}

+ (instancetype)shareDataBase
{
    static DataBaseHandle *dataBase = nil;
    if (nil == dataBase) {
        dataBase = [[DataBaseHandle alloc] init];
        
    }
    return dataBase;
}
- (void)openDB
{
    NSLog(@"%@", NSHomeDirectory());
    NSString *doc = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    NSString *fileName = [doc stringByAppendingPathComponent:@"stu.db"];
    //获取数据库
    self.db = [FMDatabase databaseWithPath:fileName];
    //判断是否打开
    if ([self.db open]) {
        NSLog(@"打开数据库成功");
    } else {
        NSLog(@"打开数据库失败");
    }
}
- (void)closeDB
{
    if ([self.db close]) {
        NSLog(@"关闭成功");
    } else {
        NSLog(@"关闭失败");
    }
    
}
- (void)createTable
{
    //打开数据库
    if ([self.db open]) {
        BOOL ret = [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS student(number integer PRIMARY KEY AUTOINCREMENT, name TEXT, sex TEXT, age integer)"];
        if (ret) {
            NSLog(@"创表成功");
        } else {
            NSLog(@"创表失败");
        }
    }
    [self.db close];
}
- (void)deleteTable
{
    if ([self.db open]) {
        BOOL ret = [self.db executeUpdate:@"DROP TABLE student"];
        if (ret) {
            NSLog(@"删表成功");
        } else {
            NSLog(@"删表失败");
        }
    }
    [self.db close];
}

- (void)insertDataWithStudent:(Student *)stu
{
    if ([self.db open]) {
        //executeUpdate: 不确定的参数用?占位
        //executeUpdateWithFormat:参数用%@,%ld等占位
        BOOL ret = [self.db executeUpdateWithFormat:@"INSERT INTO student(name, sex, age) VALUES (%@, %@, %ld)", stu.name, stu.sex, stu.age];
        //在SQLite中的text对应到的是NSString，integer对应NSNumber，blob则是NSData。该做的转换FMDB都做好了
        
//        BOOL ret = [self.db executeUpdate:@"INSERT INTO student(name, sex, age) VALUES (? , ?, ?)", stu.name, stu.sex, [NSNumber numberWithInteger:stu.age]];
        if (ret) {
            NSLog(@"添加成功");
        } else {
            NSLog(@"添加失败");
        }
    }
    [self.db close];
}
- (void)deleteDataWithNumber:(NSInteger)num
{
    if ([self.db open]) {
        //在SQLite中的text对应到的是NSString，integer对应NSNumber，blob则是NSData。该做的转换FMDB都做好了
        BOOL ret = [self.db executeUpdate:@"DELETE FROM student WHERE number = ?", [NSNumber numberWithInteger:num]];
        if (ret) {
            NSLog(@"删除成功");
        } else {
            NSLog(@"删除失败");
        }
    }
    [self.db close];
    
}
- (void)updateData:(Student *)stu num:(NSInteger)num
{
    if ([self.db open]) {
        //在SQLite中的text对应到的是NSString，integer对应NSNumber，blob则是NSData。该做的转换FMDB都做好了
        BOOL ret = [self.db executeUpdate: @"UPDATE student SET name = ?, sex = ?, age = ? WHERE number = ?", stu.name, stu.sex, [NSNumber numberWithInteger: stu.age],[NSNumber numberWithInteger: num]];
        
        if (ret) {
            NSLog(@"更新成功");
        } else {
            NSLog(@"更新失败");
        }
    }
    [self.db close];
    
}
- (NSMutableArray *)searchDB
{
    //执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"SELECT * FROM student"];
    NSMutableArray *arr = [NSMutableArray array];;
    while ([resultSet next]) {
        Student *stu = [[Student alloc] init];
        stu.name = [resultSet stringForColumn:@"name"];
        stu.sex = [resultSet stringForColumn:@"sex"];
        stu.age = [resultSet intForColumn:@"age"];
        [arr addObject:stu];
    }
    return arr;
}
- (NSMutableArray *)searchBySex:(NSString *)sex
{
    FMResultSet *resultSet = [self.db executeQuery:@"SELECT * FROM student WHERE  sex LIKE ?", sex];
    NSMutableArray *arr = [NSMutableArray array];;
    while ([resultSet next]) {
        Student *stu = [[Student alloc] init];
        stu.name = [resultSet stringForColumn:@"name"];
        stu.sex = [resultSet stringForColumn:@"sex"];
        stu.age = [resultSet intForColumn:@"age"];
        [arr addObject:stu];
    }
    return arr;
}
@end
